﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spRest_ByPointInTime]
#-- Purpose:		Provides a mechanism to restore a database to a point
#--					in time via stored procedure call
#--	Last Update:	04/23/2013
#--					For a complete history - please review comments in SVN
#-- Called By:		Restore Database Utility Executable
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spRest_ByPointInTime]
(
	@database_name			sysname,
	@stopat_time			datetime		= NULL,
	@restore_now			bit				= 0,
	@verify_only			bit				= 0,
	@destination_db			sysname			= NULL,
	@skip_pre_backup		bit				= 0
)
--WITH EXECUTE AS OWNER
AS

SET NOCOUNT ON

--- Declare Local Variables
DECLARE	@sError varchar(1000), @last_backup_set_id int, @iCount int

--- Validate Parameters
IF @skip_pre_backup IS NULL
	SET	@skip_pre_backup = 0

--- Set a default value of Now() for @stopat_time if not passed in
IF @stopat_time IS NULL
	SET	@stopat_time = GETDATE()

--- Get the latest backup since the requested date stamp
SELECT		@last_backup_set_id = MIN(backup_set_id)
FROM		[msdb].[dbo].[backupset]
WHERE		database_name = @database_name
			AND backup_start_date >= @stopat_time
			AND backup_start_date <= DATEADD(minute, 30, @stopat_time)
			AND is_copy_only = 0

--- Since there was no backup found since the requested time, look for one in the last 15 minutes (grace window)
IF @last_backup_set_id IS NULL
  BEGIN
	--- Output that the grace window is being used
	RAISERROR('No backup found since the requested timestamp.  Looking back 15 minutes.', 0, 0) WITH NOWAIT

	--- Get the latest backup within 15 minutes of the requested date stamp
	SELECT		@last_backup_set_id = MIN(backup_set_id)
	FROM		[msdb].[dbo].[backupset]
	WHERE		database_name = @database_name
				AND backup_start_date >= DATEADD(minute, -15, @stopat_time)
				AND backup_start_date <= DATEADD(minute, 30, @stopat_time)
				AND is_copy_only = 0
  END

--- Error if no backups with this label exist
IF @last_backup_set_id IS NULL
  BEGIN
	--- Determine if point-in-time is even possible for the database at the requested time
	;WITH bkp AS (
		SELECT		TOP 1 *
		FROM		[msdb].[dbo].[backupset]
		WHERE		database_name = @database_name
					AND backup_start_date < @stopat_time
					AND is_copy_only = 0
		ORDER BY	backup_start_date DESC

		UNION

		SELECT		TOP 1 *
		FROM		[msdb].[dbo].[backupset]
		WHERE		database_name = @database_name
					AND backup_start_date >= @stopat_time
					AND is_copy_only = 0
		ORDER BY	backup_start_date
	)
	SELECT		@iCount = COUNT(*)
	FROM		bkp
	WHERE		(
					recovery_model IN ('FULL', 'BULK-LOGGED')
					AND type = 'L'
				)
				OR (
					recovery_model = 'SIMPLE'
					AND backup_start_date = @stopat_time
				)

	IF @iCount = 0
	  BEGIN
		SELECT	@sError = 'The database model at ' + QUOTENAME(CONVERT(varchar(50), @stopat_time)) + ' did not support point-in-time recovery.'
	  END
	ELSE
	  BEGIN
		SELECT	@sError = 'No backups of ' + QUOTENAME(@database_name) + ' were found on or after ' + QUOTENAME(CONVERT(varchar(50), DATEADD(minute, -15, @stopat_time)))
	  END

	RAISERROR(@sError, 16, 1)
	RETURN
  END

--- Execute the Restore
EXEC	[dbo].[spRest_ByBackupSetID]	@database_name = @database_name, @last_backup_set_id = @last_backup_set_id,
										@restore_now = @restore_now, @verify_only = @verify_only, 
										@destination_db = @destination_db, @stopat_time = @stopat_time,
										@skip_pre_backup = @skip_pre_backup

SET NOCOUNT OFF
